﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using ElectronicJournal.DB.Item;

namespace ElectronicJournal.DB
{
    public class DbNews
    {
        SqlDb db;

        public DbNews()
        {
            db = new SqlDb();
        }

        public string QUERY { get; set; }

        private ItemNews FetchRowNews(DataRow dr)
        {
            return new ItemNews()
            {
                idNews = Convert.ToInt32(dr["idNews"]),
                title = dr["title"].ToString(),
                content = dr["content"].ToString(),
                date = Convert.ToDateTime(dr["date"])
            };
        }

        public ItemNews GetNews(int idNews)
        {
            DataTable dt = db.Read(String.Format("SELECT * FROM News WHERE idNews='{0}'", idNews));
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                return FetchRowNews(dr);
            }
            return null;

        }

        public IEnumerable<ItemNews> GetNewsList(int page, int limit)
        {
            DataTable dt = db.Read(String.Format("SELECT * FROM ( SELECT TOP ({0})  * FROM ( SELECT TOP ({1}) * FROM News ORDER BY 'date' DESC ) AS t1 ORDER BY 'date' ASC ) AS t2 ORDER BY 'date' DESC  ", limit, limit * (page + 1)));
            foreach (DataRow dr in dt.Rows)
            {
                yield return FetchRowNews(dr);
            }

        }

        public IEnumerable<ItemNews>  GetAll()
        {
            DataTable dt = db.Read(String.Format("SELECT * FROM News ORDER BY 'date' DESC"));
            foreach (DataRow dr in dt.Rows)
            {
                yield return FetchRowNews(dr);
            }

        }

        public bool Add(ItemNews news)
        {

            QUERY = String.Format("INSERT INTO News VALUES ('{0}','{1}', '{2}')",
                news.title, news.content, news.date.ToString("MM.dd.yyyy H:mm:ss"));
            db.Query(QUERY);

            return true;

        }

        public bool Update(ItemNews news)
        {

            QUERY = String.Format("UPDATE News SET title='{0}',content='{1}',date='{2}' WHERE idNews='{3}'",
                news.title, news.content, news.date.ToString("MM.dd.yyyy H:mm:ss"), news.idNews);
            db.Query(QUERY);

            return true;

        }

        public bool Remove(int idNews)
        {
            QUERY = String.Format("DELETE FROM News WHERE idNews='{0}'", idNews);
            db.Query(QUERY);
            return true;
        }
    }
}